package com.engine.query.cmd;

import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.PagerUtils;
import com.alibaba.fastjson.JSONObject;
import com.engine.core.gen.DbPlayload;
import com.engine.core.gen.ParamOption;
import com.engine.core.interceptor.JdbcCommandContext;
import com.engine.core.interceptor.ResponseCommand;
import com.engine.core.rowMapper.JdbcTypeBiz;
import com.engine.core.rowMapper.MappedType;
import com.engine.core.util.JdbcTemplateUtils;
import com.engine.core.util.QueryUtil;
import com.engine.core.util.Response;
import com.engine.core.util.Utils;
import com.engine.system.User;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.*;

import static com.engine.core.exception.ContainsSqlInjection.checkSqlStr;

public class QueryListCommand extends ResponseCommand {
    public QueryListCommand(User user, Map<String, Object> params) {
        super(user, params);
    }

    public static  class Playload extends DbPlayload<Playload> {


        private int paramLength=1;


        private Playload(User user) {
            super(true,user);
        }


        public static Playload newInstance(User user){
            Playload playload=new Playload(user);
            playload.queryParamsForce=true;
            return playload;
        }
        /**
         * @return
         */
        @Override
        public Map<String, Object> getParams() {
            Map<String,Object> params=new HashMap<>();
            params.put(QueryUtil.QUERY_PARAMS_ISCONVERT,isConvertParam?"1":"0");
            if(isConvertParam){
                params.putAll(paramsMap);
            }else{
                params.put(QueryUtil.QUERY_PARAMS,paramsMap);
            }

            params.put(QueryUtil.QUERY_COLUMNS,queryColumns);
            params.put(QueryUtil.QUERY_WHERE,queryWhere);
            params.put(QueryUtil.QUERY_FROM,queryFrom);
            params.put(QueryUtil.QUERY_ORDERBY,queryOrderBy);
            params.put(QueryUtil.QUERY_GROUPBY,queryGroupBy);
            params.put(QueryUtil.QUERY_HAVING,queryHaving);
            params.put(QueryUtil.QUERY_FETHCHRESULT,fethchResult?"1":"0");
            params.put(QueryUtil.GET_QUERY_PARAMS_FORCE,"1");
            params.put(QueryUtil.QUERY_RESULT_SQL,querySql);
            params.put(QueryUtil.QUERY_PARAMS_MAP,isMapParam?"1":"0");


            params.put(QueryUtil.QUERY_SQL_REPLACE,addReplace);
            return params;
        }

        public Playload columns(String queryColumns){
            this.queryColumns=queryColumns;
            return this;
        }

        public Playload where(String queryWhere){
            this.queryWhere=queryWhere;
            return this;
        }

        public Playload fethchResult(boolean fethchResult){
            this.fethchResult=fethchResult;
            return this;
        }

        public Playload from(String from){
            this.queryFrom=from;
            return this;
        }

        public Playload isMapParam(boolean isMapParam){
            this.isMapParam=isMapParam;
            return this;
        }

        public Playload isConvertParam(boolean isConvertParam){
            this.isConvertParam=isConvertParam;
            return this;
        }


        public Playload orderBy(String orderBy){
            this.queryOrderBy=orderBy;
            return this;
        }

        public Playload groupBy(String groupBy){
            this.queryGroupBy=groupBy;
            return this;
        }

        public Playload having(String having){
            this.queryHaving=having;
            return this;
        }

        public Playload sql(String querySql){
            this.querySql=querySql;
            return this;
        }

        public Playload addParam(String key,Object value){
            paramsMap.put("P_"+key,value);
            return this;
        }

        public Playload addParam(Object[] value){
            for (int i = 0; i < value.length; i++) {
                addParam(value);
            }
            return this;
        }

        public Playload addParam(Object value){
            paramsMap.put("P_"+paramLength,value);
            paramLength++;
            return this;
        }

        public Playload addReplacement(String key,String replacement){
            checkSqlStr(replacement);
            addReplace.put(key,replacement);
            return this;
        }

        public Playload addReplacements(Map<String,String> replacements){
            Collection<String> values=replacements.values();
            for (String v:values) {
                checkSqlStr(v);
            }
            addReplace.putAll(replacements);
            return this;
        }

        public Playload addParams(Map<String,Object> params){
            paramsMap.putAll(params);
            return this;
        }

        /**
         * @param map
         * @return
         */
        @Override
        public Playload putAll(Map<String, Object> map) throws Exception{
             super.putAll(map);
            paramLength=JdbcTypeBiz.getQueryParamsLength(map);
            return this;
        }

        /**
         * @param commandContext
         * @return
         * @throws Exception
         */
        @Override
        public Response execute(JdbcCommandContext commandContext) throws Exception {
            return new QueryListCommand(user,this.getParams()).execute(commandContext);
        }
    }

    /**
     * @param commandContext
     * @return
     * @throws Exception
     */
    @Override
    public Response execute(JdbcCommandContext commandContext){
        Map<Integer, String>  typeFormat=commandContext.getGlobal().getTypeFormat();
        String[] dateformat=commandContext.getGlobal().getDateformat();
        JdbcTemplate jdbcTemplate=commandContext.getJdbcTemplate();
        String method=Utils.nullDefault((String)params.get(QueryUtil.QUERY_METHOD),"qget");
        String fethchResult=Utils.nullDefault((String)params.get(QueryUtil.QUERY_FETHCHRESULT),"1");

        Map<String, MappedType> formatMap=new HashMap<>();
        ParamOption paramOption=new ParamOption(params);
        Object queryParams=new Object[0];

        String columns= Utils.urlDecode((String)params.get(QueryUtil.QUERY_COLUMNS),paramOption);
        String where= Utils.urlDecode((String)params.get(QueryUtil.QUERY_WHERE),paramOption);
        String orderBy= Utils.urlDecode((String)params.get(QueryUtil.QUERY_ORDERBY),paramOption);
        String groupBy= Utils.urlDecode((String)params.get(QueryUtil.QUERY_GROUPBY),paramOption);
        String having= Utils.urlDecode((String)params.get(QueryUtil.QUERY_HAVING),paramOption);
        String from=Utils.urlDecode((String)params.get(QueryUtil.QUERY_FROM),paramOption);
        String isMapParam=Utils.nullDefault((String)params.get(QueryUtil.QUERY_PARAMS_MAP),"0");
        String sql=Utils.urlDecode((String)params.get(QueryUtil.QUERY_RESULT_SQL),paramOption);
        String isConvertParam=Utils.nullDefault((String)params.get(QueryUtil.QUERY_PARAMS_ISCONVERT),"1");
        String queryParamsForce=Utils.nullDefault((String)params.get(QueryUtil.GET_QUERY_PARAMS_FORCE),"1");
        if(method.equalsIgnoreCase(QueryUtil.QUERY_METHOD_POST) && Utils.nullDefault((String)params.get(QueryUtil.GET_QUERY_PARAMS_FORCE),"0").equalsIgnoreCase("0")){
            JSONObject paramRoot=JSONObject.parseObject((String)params.get(QueryUtil.QUERY_CONTENT));
            columns= Utils.urlDecode(paramRoot.getString(QueryUtil.QUERY_COLUMNS),paramOption);
            where= Utils.urlDecode(paramRoot.getString(QueryUtil.QUERY_WHERE),paramOption);
            orderBy= Utils.urlDecode(paramRoot.getString(QueryUtil.QUERY_ORDERBY),paramOption);
            groupBy= Utils.urlDecode(paramRoot.getString(QueryUtil.QUERY_GROUPBY),paramOption);
            having= Utils.urlDecode(paramRoot.getString(QueryUtil.QUERY_HAVING),paramOption);
            from=Utils.urlDecode(paramRoot.getString(QueryUtil.QUERY_FROM),paramOption);
            isMapParam=Utils.nullDefault(paramRoot.getString(QueryUtil.QUERY_PARAMS_MAP),"0");
            sql=Utils.urlDecode((String)paramRoot.getString(QueryUtil.QUERY_RESULT_SQL),paramOption);
        }
        Response response=Response.static_success("执行成功!");
        StringBuilder sqlBuilder=new StringBuilder();
         if(Utils.nullDefault(sql,"").equals("")){
            sqlBuilder.append("select ");
            sqlBuilder.append(columns);
            sqlBuilder.append(" from ");
            sqlBuilder.append(from);
            if(StringUtils.isNotBlank(where)){
                sqlBuilder.append(" where ");
                sqlBuilder.append(where);
            }
            if(StringUtils.isNotBlank(groupBy)){
                sqlBuilder.append(" group by ");
                sqlBuilder.append(groupBy);
                if(StringUtils.isNotBlank(having)){
                    sqlBuilder.append(" HAVING ");
                    sqlBuilder.append(having);
                }
            }
            if(StringUtils.isNotBlank(orderBy)){
                sqlBuilder.append(" order by ");
                sqlBuilder.append(orderBy);
            }
            sql=sqlBuilder.toString();
        }

        Map<String,String> sqlReplacementMap=(Map<String,String>)params.get(QueryUtil.QUERY_SQL_REPLACE);
        if(sqlReplacementMap!=null){
            Iterator<Map.Entry<String,String>> sqlReplacementMapIterator=sqlReplacementMap.entrySet().iterator();
            while(sqlReplacementMapIterator.hasNext()){
                Map.Entry<String,String> sqlReplacementMapEntry=sqlReplacementMapIterator.next();
                sql=sql.replaceAll("\\$"+sqlReplacementMapEntry.getKey()+"\\$",sqlReplacementMapEntry.getValue());
            }
        }
        response.set(QueryUtil.QUERY_RESULT_SQL,sql);
        try {
            if(method.equalsIgnoreCase(QueryUtil.QUERY_METHOD_POST) && queryParamsForce.equals("0")){
                JSONObject paramRoot=JSONObject.parseObject((String)params.get(QueryUtil.QUERY_CONTENT));
                if(isMapParam.equals("1")){
                    queryParams=JdbcTypeBiz.getQueryParamsMap(paramRoot.getJSONObject(QueryUtil.QUERY_RESULT_PARAMS),dateformat);
                }else {
                    queryParams=JdbcTypeBiz.getQueryParams(paramRoot.getJSONObject(QueryUtil.QUERY_RESULT_PARAMS),dateformat);
                }
            }else{
                if(isConvertParam.equals("1")) {
                    if (isMapParam.equals("1")) {
                        queryParams = JdbcTypeBiz.getQueryParamsMap(params, dateformat);
                    } else {
                        queryParams = JdbcTypeBiz.getQueryParams(params, dateformat);
                    }
                }else{
                    queryParams=(Map<String,Object>)params.get(QueryUtil.QUERY_PARAMS);
                    if (isMapParam.equals("1")) {
                        ((Map<String,Object>)queryParams).putAll(JdbcTypeBiz.getQueryParamsMap(params, dateformat));
                    }
                }
            }
            response.set(QueryUtil.QUERY_PARAMS,queryParams);

            PagerUtils.limit(sql, DbType.mysql, 0, 100);

            if(Utils.nullDefault(fethchResult,"1").equalsIgnoreCase("1")){
                if(Utils.nullDefault(sql,"").equals("")){
                    List<Map<String,Object>> returnList= JdbcTemplateUtils.fetchResult(sql,jdbcTemplate,formatMap,typeFormat,
                            queryParams);
                    response.set(QueryUtil.QUERY_RESULT,returnList);
                }else{
                    List<Map<String,Object>> returnList= JdbcTemplateUtils.fetchResult(sql,jdbcTemplate,formatMap,typeFormat,
                            queryParams);
                    response.set(QueryUtil.QUERY_RESULT,returnList);
                }
            }
            return response;
        } catch (Exception e) {
            response.setErr(e);
            return response;
        }
    }

    public static void main(String[] args) {
    }
}
